

clear
set more 1
set type double

*organize files
*change peg to using current exchange rate data

*****************************************************************
*VARIABLES
*1. manufacturing firm indicator
*2. usd peg
*3. rauch
*******************************************************************************************


******************************************************
*1. constructs manufacturing indicator at firm level
*******************************************************
*based on nace codes 10 to 33
*some duplicates so make them nonman if ever report nonman
*1.36% obs both in manuf and nonmanuf
*this only goes up to 2017
**************************************

use "../../rawdata/annual accounts update", clear 
	keep if year>=2012 
	keep vat nacerev2
	drop if missing(nacerev2)
	duplicates drop
	rename nacerev2 nace2
	drop if vat==0|missing(vat)
	gen manuf_firm=nace2>=10 & nace2<=33 
	keep vat manuf_firm
	duplicates drop
	duplicates tag vat, gen(tag)
	tab tag
	keep if tag==0
	drop tag
	label var manuf_firm "nacerev2 >=10 & <=33 using accounts data"
save ../data/manuf_firm, replace
	

*********************************************
* Identify US Pegged countries
*********************************************
*use ../rawdata/xr_monthly_2019m4_AVG, clear
use ../rawdata/xr_monthly_2020, clear
	keep cty_name cty_code year month xr_m
	drop if year<2012|year>2020
	drop if year<2012
	egen cty_code_num=group(cty_code)
	egen t=group(year month)
	tsset cty_code_num t
	gen dxr = ln(xr_m/l.xr_m)
	drop if missing(dxr)

	gen x=1
	bys cty_code: egen sumx=sum(x)
	

* Get USD change vis a vis EUR
	preserve
		keep if cty_code == "US"
		rename dxr dxr_us
		save temp/dxr_us, replace
	restore

	merge m:1 month year using temp/dxr_us, nogen


* Obtain root mean squared of currency evolution vis a vis EUR versus USD evolution vis a vis EUR
	gen diff_dxr_sq = (dxr - dxr_us)^2
	gen n = 1

	collapse (sum) n diff_dxr_sq, by(cty_code cty_name)

*adjust rmse for monthly data	
	gen rmse = sqrt(diff_dxr_sq/n)
	replace rmse=rmse*(12^0.5)
	
* Subdivide into pegs and non-pegs
	gen usd_peg =  rmse < 0.05
	keep cty_code cty_name rmse usd_peg 
save ../data/usd_peg, replace	

***********************************************
*Notes: maps Rauch classification to HS6
*Rauch classification is at SITC 4-digit rev 2, which maps to HS2012
*We need it to map to HS2012
***********************************

*0. Rauch concordance is at SITC 4-digit revision 2
****************************************

*1. Rauch classification at SITC 4-digit revision 2
import excel ../rawdata/rauch_classification_rev2.xlsx, firstrow clear
rename sitc4 sitc
save temp/rauch_classification, replace

*2. Mapping HS2012 to SITC rev2, merge with Rauch classification
import excel ../rawdata/HS2012_to_SITC2.xls, firstrow sheet("Conversion HS 2012-SITC 2") cellrange(C7) clear
	drop E F G H
	rename HS2012 HS12
	rename SITC2 S2
	gen sitc4 = substr(S2,1,4) 
	replace sitc4 = sitc4 + "0" if strlen(sitc4)==3
	
	gen sitc3 = substr(S2,1,3)
	replace sitc3 = sitc3 + "0" 
	
	rename sitc4 sitc
	merge m:1 sitc using temp/rauch_classification, keepusing(con lib) keep(1 3) nogen
	
	* Some Rauch classifications only exist at the 3-digit level, fill in those when 4 digit classification unavailable
	rename (sitc sitc3) (sitc4 sitc)
	rename (con lib) (con_1 lib_1)
	merge m:1 sitc using temp/rauch_classification, keepusing(con lib) keep(1 3) nogen
	
	gen con_use = con_1 if !mi(con_1) 
	gen lib_use = lib_1 if !mi(lib_1)
	replace con_use = con if mi(con_1) & !mi(con)
	replace lib_use = lib if mi(lib_1) & !mi(lib)
	
	drop con_1 lib_1 con lib
	
	rename *_use *
	
	rename sitc sitc3
	drop S2 sitc3 
	drop if missing(HS12) 
	drop if missing(con) 
	duplicates drop
	
save temp/Rauch_concordance, replace

*3. Map HS2017 codes that changed from 2012 revision, merge with Rauch concordance
**************************************
	
import excel ../rawdata/HS2017toHS2012ConversionAndCorrelationTables.xlsx, firstrow sheet("Conversion HS17-HS12") clear
	rename (FromHS2017 ToHS2012) (hs6 HS12)
	assert strlen(hs6)==6
	assert strlen(HS12)==6
	
	duplicates drop
	drop if hs6==HS12
	
	merge m:1 HS12 using temp/Rauch_concordance, keep(master match) nogen
	keep hs6 con lib
	drop if missing(hs6)
	duplicates drop
	rename hs6 HS12
save temp/Rauch_HS2017, replace

use temp/Rauch_concordance, clear
	keep HS12 con lib
	append using temp/Rauch_HS2017
	* Check to make sure no duplicate HS entries
	bys HS12: gen n=_N
	assert n==1
	drop n
	rename HS12 hs6
	duplicates drop
	
*some HS6 still missing classification, so do these manually based on related products
*codes missing rauch classification

*711890 (coins) are classified as w
*special codes starting with 98 and 99 - make them n
cap program drop addobs
program addobs
	args hs6 con lib
	set obs `=_N+1'
	replace hs6 = "`hs6'" if _n==_N
	replace con = "`con'" if _n==_N
	replace lib = "`lib'" if _n==_N
end

*      hs6      con lib
addobs "711890" "w" "w"
addobs "988039" "n" "n"
addobs "988068" "n" "n"
addobs "988072" "n" "n"
addobs "988073" "n" "n"
addobs "988076" "n" "n"
addobs "988084" "n" "n"
addobs "988085" "n" "n"
addobs "988090" "n" "n"
addobs "993024" "n" "n"
addobs "993027" "n" "n"
addobs "993099" "n" "n"
addobs "994500" "n" "n"
addobs "988038" "n" "n"
addobs "988040" "n" "n"
addobs "988049" "n" "n"
addobs "988074" "n" "n"
addobs "988082" "n" "n"
addobs "988094" "n" "n"
addobs "995000" "n" "n"


label var hs6 "6-digit HS codes from 2017 and 2012 HS revisions"
label var con "Rauch 'conservative' classification, n is diff, r and w are homog "
label var lib "Rauch 'liberal' classification, n is diff, r and w are homog"

	
save ../data/Rauch_hs6_concordance, replace


*erase temp files
erase temp/dxr_us.dta
erase temp/Rauch_concordance.dta

